Salesforce Connector DATE Functions

CURRENT_DATE()

Returns the current date value.

SELECT CURRENT_DATE();
-- Result: 2018-02-01

CURRENT_TIMESTAMP()

Returns the current time stamp of the database system as a datetime value. This value is equal to GETDATE and SYSDATETIME, and is always in the local timezone.

SELECT CURRENT_TIMESTAMP();
-- Result: 2018-02-01 03:04:05

DATEADD(datepart, integer_number, date[, dateformat])

Returns the datetime value that results from adding the specified number (a signed integer) to the specified date part of the date.

• datepart: The part of the date to add the specified number to. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
• number: The number to be added.
• date: The expression of the datetime data type.
• dateformat: The optional output date format.

SELECT DATEADD('d', 5, '2018-02-01');
-- Result: 2018-02-06

SELECT DATEADD('hh', 5, '2018-02-01 00:00:00');
-- Result: 2018-02-01 05:00:00

DATEDIFF(datepart, startdate, enddate)

Returns the difference (a signed integer) of the specified time interval between the specified start date and end date.

• datepart: The part of the date that is the time interval of the difference between the start date and end date. The valid values and abbreviations are day (dd, d), hour (hh), minute (mi, n), second (ss, s), and millisecond (ms).
• startdate: The datetime expression of the start date.
• enddate: The datetime expression of the end date.

SELECT DATEDIFF('d', '2018-02-01', '2018-02-10');
-- Result: 9

SELECT DATEDIFF('hh', '2018-02-01 00:00:00', '2018-02-01 12:00:00');
-- Result: 12

DATEFROMPARTS(integer_year, integer_month, integer_day)

Returns the datetime value for the specified year, month, and day.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.

SELECT DATEFROMPARTS(2018, 2, 1);
-- Result: 2018-02-01

DATENAME(datepart, date)

Returns the character string that represents the specified date part of the specified date.

• datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), and TZoffset (tz).
• date: The datetime expression.

SELECT DATENAME('yy', '2018-02-01');
-- Result: '2018'

SELECT DATENAME('dw', '2018-02-01');
-- Result: 'Thursday'

DATEPART(datepart, date[, integer_datefirst])

Returns a character string that represents the specified date part of the specified date.

• datepart: The part of the date to return. The valid values and abbreviations are year (yy, yyyy), quarter (qq, q), month (mm, m), dayofyear (dy, y), day (dd, d), week (wk, ww), weekday (dw), hour (hh), minute (mi, n), second (ss, s), millisecond (ms), microsecond (mcs), nanosecond (ns), TZoffset (tz), ISODOW, ISO_WEEK (isoweek, isowk,isoww), and ISOYEAR.
• date: The datetime string.
• datefirst: The optional integer representing the first day of the week. The default is 7, Sunday.

SELECT DATEPART('yy', '2018-02-01');
-- Result: 2018

SELECT DATEPART('dw', '2018-02-01');
-- Result: 5

DATETIME2FROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the datetime value for the specified date parts.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• fractions: The integer expression specifying the fractions of the second.
• precision: The integer expression specifying the precision of the fraction.

SELECT DATETIME2FROMPARTS(2018, 2, 1, 1, 2, 3, 456, 3);
-- Result: 2018-02-01 01:02:03.456

DATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute, integer_seconds, integer_milliseconds)

Returns the datetime value for the specified date parts.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• milliseconds: The integer expression specifying the milliseconds.

SELECT DATETIMEFROMPARTS(2018, 2, 1, 1, 2, 3, 456);
-- Result: 2018-02-01 01:02:03.456

DAY(date)

Returns the integer that specifies the day component of the specified date.

• date: The datetime string that specifies the date.

SELECT DAY('2018-02-01');
-- Result: 1

EOMONTH(start_date[, integer_month_to_add])

Returns the last day of the month that contains the specified date with an optional offset.

• start_date: The datetime expression specifying the date for which to return the last day of the month.
• month_to_add: The optional integer expression specifying the number of months to add to start_date.

SELECT EOMONTH('2018-02-01');
-- Result: 2018-02-28

SELECT EOMONTH('2018-02-01', 2);
-- Result: 2018-04-30

GETDATE()

Returns the current time stamp of the database system as a datetime value. This value is equal to CURRENT_TIMESTAMP and SYSDATETIME, and is always in the local timezone.

SELECT GETDATE();
-- Result: 2018-02-01 03:04:05

GETUTCDATE()

Returns the current time stamp of the database system formatted as a UTC datetime value. This value is equal to SYSUTCDATETIME.

SELECT GETUTCDATE();
-- For example, if the local timezone is Eastern European Time (GMT+2)
-- Result: 2018-02-01 05:04:05

ISDATE(date[, date_format])

Returns 1 if the value is a valid date, time, or datetime value; otherwise, 0.

• date: The datetime string.
• date_format: The optional datetime format.

SELECT ISDATE('2018-02-01', 'yyyy-MM-dd');
-- Result: 1

SELECT ISDATE('Not a date');
-- Result: 0

SMALLDATETIMEFROMPARTS(integer_year, integer_month, integer_day, integer_hour, integer_minute)

Returns the datetime value for the specified date and time.

• year: The integer expression specifying the year.
• month: The integer expression specifying the month.
• day: The integer expression specifying the day.
• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.

SELECT SMALLDATETIMEFROMPARTS(2018, 2, 1, 1, 2);
-- Result: 2018-02-01 01:02:00

SYSDATETIME()

Returns the current time stamp as a datetime value of the database system. It is equal to GETDATE and CURRENT_TIMESTAMP, and is always in the local timezone.

SELECT SYSDATETIME();
-- Result: 2018-02-01 03:04:05

SYSUTCDATETIME()

Returns the current system date and time as a UTC datetime value. It is equal to GETUTCDATE.

SELECT SYSUTCDATETIME();
-- For example, if the local timezone is Eastern European Time (GMT+2)
-- Result: 2018-02-01 05:04:05

TIMEFROMPARTS(integer_hour, integer_minute, integer_seconds, integer_fractions, integer_precision)

Returns the time value for the specified time and with the specified precision.

• hour: The integer expression specifying the hour.
• minute: The integer expression specifying the minute.
• seconds: The integer expression specifying the seconds.
• fractions: The integer expression specifying the fractions of the second.
• precision : The integer expression specifying the precision of the fraction.

SELECT TIMEFROMPARTS(1, 2, 3, 456, 3);
-- Result: 01:02:03.456

YEAR(date)

Returns the integer that specifies the year of the specified date.

• date: The datetime string.

SELECT YEAR('2018-02-01');
-- Result: 2018